package cn.com.server;

import java.io.FileWriter;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import cn.com.server.common.util.YamlUtils;

public class DBHelp {

	@SuppressWarnings("unchecked")
	public static void main(String[] args) {
		List<Map<String, Object>> allMap = new ArrayList<>();
		LinkedHashMap<String, Object> map = YamlUtils.getYamlSource("db_help.yml");
		String tablesSql = "SELECT " + "	TABLE_NAME, " + "	TABLE_COMMENT " + "FROM "
				+ "	information_schema.`TABLES` " + "WHERE " + "	TABLE_SCHEMA = \""
				+ ((LinkedHashMap<String, Object>) map.get("jdbc")).get("database") + "\"";

		System.out.println(tablesSql);
		List<Map<Object, Object>> tablesMap = getMap(tablesSql, map);

		for (Map<Object, Object> m : tablesMap) {
			String cloumSql = "SELECT " + "COLUMN_COMMENT, " + "COLUMN_NAME, " + "DATA_TYPE, " + "COLUMN_TYPE, "
					+ "IS_NULLABLE, " + "COLUMN_DEFAULT, " + "COLUMN_KEY, " + "EXTRA, " + "CHARACTER_MAXIMUM_LENGTH, "
					+ "CHARACTER_OCTET_LENGTH, " + "NUMERIC_PRECISION, " + "NUMERIC_SCALE " + "FROM "
					+ "	information_schema.`COLUMNS` " + "WHERE " + "	TABLE_SCHEMA = \""
					+ ((LinkedHashMap<String, Object>) map.get("jdbc")).get("database") + "\" " + "AND TABLE_NAME = \""
					+ m.get("TABLE_NAME") + "\" " + "ORDER BY ORDINAL_POSITION";
			System.out.println(cloumSql);
			List<Map<Object, Object>> cloumsMap = getMap(cloumSql, map);
			Map<String, Object> am = new HashMap<>();
			am.put("table", m);
			am.put("cloum", cloumsMap);
			allMap.add(am);
		}
		String daoPackage = (String) map.get("daoPackage");
		String classPackage = (String) map.get("classPackage");
		String tabName = "tb_sb_";
		for (Map<String, Object> tc : allMap) {

			// 判断import需要导入的类
			// 私有成员声明
			// getter setter生成
			String importString = "package " + classPackage + ";\r\n\r\nimport java.io.Serializable;\r\n";
			String classTxt = "";
			String privateString = "";
			String getSetString = "";
			
//			String toString = "\r\n" + 
//					"	@Override\r\n" + 
//					"	public String toString() {\r\n" + 
//					"		return \"UserRole{\" +\r\n" + 
//					"			\"id=\" + id +\r\n" + 
//					"			\", userId=\" + userId +\r\n" + 
//					"			\", roleId=\" + roleId +\r\n" + 
//					"			\", status=\" + status +\r\n" + 
//					"			\", createTime=\" + createTime +\r\n" + 
//					"			\"}\";\r\n" + 
//					"	}";

			String tableCm = "";
			String tbName = (String) ((Map<String, Object>) (tc.get("table"))).get("TABLE_NAME");
			if (tbName.startsWith(tabName)) {
				tbName = tbName.replaceFirst(tabName, "");
			}
			try {
				tableCm = (String) ((Map<String, Object>) (tc.get("table"))).get("TABLE_COMMENT");
				if (tableCm == null || "".equals(tableCm)) {
					tableCm = underline2Camel(tbName, false);
				}
			} catch (Exception e) {
				tableCm = (String) ((Map<String, Object>) (tc.get("table"))).get("TABLE_NAME");
			}
			classTxt = classTxt + "/**\r\n" + " * <p>\r\n" + " * " + tableCm + "\r\n" + " * </p>\r\n" + " *\r\n"
					+ " * @author qing\r\n" + " * @since 2017-11-15\r\n" + " */\r\n"
					+ "@SuppressWarnings(\"serial\")\r\n" + "public class " + underline2Camel(tbName, false)
					+ " implements Serializable {\r\n";
			// xml
			// xml
			String xmltop = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\r\n"
					+ "<!DOCTYPE mapper PUBLIC \"-//mybatis.org//DTD Mapper 3.0//EN\" \"http://mybatis.org/dtd/mybatis-3-mapper.dtd\">\r\n";
			xmltop = xmltop + "<mapper namespace=\"" + daoPackage + "." + underline2Camel(tbName, false) + "Dao\">"
					+ "\r\n	<!-- 通用查询映射结果 -->\r\n" + "	<resultMap id=\"BaseResultMap\" type=\"" + classPackage + "."
					+ underline2Camel(tbName, false) + "\">\r\n";
			String xmlcontent = "";

			List<Map<Object, Object>> lm = (List<Map<Object, Object>>) tc.get("cloum");
			for (Map<Object, Object> om : lm) {
				String mysqltype = (String) om.get("DATA_TYPE");
				String classType = (String) ((LinkedHashMap<String, Object>) map.get("mysqlTypes"))
						.get(mysqltype.toLowerCase());
				String privateName = (String) om.get("COLUMN_NAME");
				String remark = (String) om.get("COLUMN_COMMENT");
				if (classType.indexOf(".") != -1) {
					if (importString.indexOf(classType) == -1) {
						importString = importString + "import " + classType + ";\r\n";
					}
					classType = classType.substring(classType.lastIndexOf(".") + 1, classType.length());
					if("Date".equals(classType)) {
						if(importString.indexOf("com.fasterxml.jackson.annotation.JsonFormat") == -1) {
							importString = importString + "import com.fasterxml.jackson.annotation.JsonFormat;\r\n";
						}
					}
				}
				remark = (remark == null || "".equals(remark)) ? privateName : remark;
				privateString = privateString + "\r\n   /**\r\n" + "     * " + remark + "\r\n" + "     */" + "\r\n"
						+ "	private " + classType + " " + underline2Camel(privateName, true) + ";";
				getSetString = getSetString + "\r\n"
						+ ("Date".equals(classType)
								? "	@JsonFormat(pattern=\"yyyy-MM-dd HH:mm:ss\",locale=\"zh\",timezone = \"GMT+8\")\r\n"
								: "")
						+ "	public " + classType + " get" + underline2Camel(privateName, false) + "() {\r\n"
						+ "		return " + underline2Camel(privateName, true) + ";\r\n" + "	}\r\n" + "\r\n"
						+ "	public void set" + underline2Camel(privateName, false) + "(" + classType + " "
						+ underline2Camel(privateName, true) + ") {\r\n" + "		this."
						+ underline2Camel(privateName, true) + " = " + underline2Camel(privateName, true) + ";\r\n"
						+ "	}";
				xmlcontent = xmlcontent + ("PRI".equals((String) om.get("COLUMN_KEY"))
						? ("		<id column=\"" + privateName + "\" property=\"" + underline2Camel(privateName, true)
								+ "\" />\r\n")
						: ("		<result column=\"" + privateName + "\" property=\""
								+ underline2Camel(privateName, true) + "\" />\r\n"));
			}
			try {
				FileWriter fw = new FileWriter(System.getProperty("user.dir")+"/文档/entity/" + underline2Camel(tbName, false) + ".java");
				FileWriter fx = new FileWriter(System.getProperty("user.dir")+"/文档/xml/" + underline2Camel(tbName, false) + ".xml");
				String s = importString + "\r\n" + classTxt + "\r\n" + privateString + "\r\n" + getSetString + "\r\n}";
				fw.write(s, 0, s.length());
				fw.flush();
				fw.close();
				String xml = xmltop + xmlcontent + "	</resultMap>\r\n</mapper>";
				fx.write(xml, 0, xml.length());
				fx.flush();
				fx.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
			//File f = new File("D:\\Users\\orange\\git\\src/mian/java/cn");
			//System.out.println(f.getAbsolutePath());
			//f.mkdirs();
		}
	}

	/**
	 * 下划线转驼峰法
	 * 
	 * @param line
	 *            源字符串
	 * @param smallCamel
	 *            大小驼峰,是否为小驼峰
	 * @return 转换后的字符串
	 */
	public static String underline2Camel(String line, boolean smallCamel) {
		if (line == null || "".equals(line)) {
			return "";
		}
		StringBuffer sb = new StringBuffer();
		Pattern pattern = Pattern.compile("([A-Za-z\\d]+)(_)?");
		Matcher matcher = pattern.matcher(line);
		while (matcher.find()) {
			String word = matcher.group();
			sb.append(smallCamel && matcher.start() == 0 ? Character.toLowerCase(word.charAt(0))
					: Character.toUpperCase(word.charAt(0)));
			int index = word.lastIndexOf('_');
			if (index > 0) {
				sb.append(word.substring(1, index).toLowerCase());
			} else {
				sb.append(word.substring(1).toLowerCase());
			}
		}
		return sb.toString();
	}

	public static Connection getConnect(LinkedHashMap<String, Object> properties) {
		Connection con = null;
		@SuppressWarnings("unchecked")
		LinkedHashMap<String, Object> jp = (LinkedHashMap<String, Object>) properties.get("jdbc");
		try {
			Class.forName((String) jp.get("driver"));
			con = DriverManager.getConnection(
					"jdbc:mysql://" + (String) jp.get("ip") + ":" + (Integer) jp.get("port") + "/"
							+ (String) jp.get("database") + "?" + (String) jp.get("params"),
					(String) jp.get("user"), (String) jp.get("password"));
		} catch (Exception e) {
			e.printStackTrace();
		}
		return con;
	}

	public static List<Map<Object, Object>> getMap(String sql, LinkedHashMap<String, Object> map) {
		try {
			long timestart = System.currentTimeMillis();
			Connection connection = getConnect(map);
			Statement s = connection.createStatement();
			ResultSet rs = s.executeQuery(sql);
			ResultSetMetaData rsmd = rs.getMetaData();
			int rows = rsmd.getColumnCount();
			List<Map<Object, Object>> rsMap = new ArrayList<Map<Object, Object>>();
			while (rs.next()) {
				Map<Object, Object> fieldValue = new HashMap<Object, Object>();
				for (int i = 1; i <= rows; i++) {
					String fieldClassName = rsmd.getColumnClassName(i);
					String fieldName = rsmd.getColumnName(i);
					encasementMapping(fieldClassName, fieldName, rs, fieldValue);
				}
				rsMap.add(fieldValue);
			}
			rs.close();
			s.close();
			connection.close();
			System.out.println("获取数据花费时间：" + (System.currentTimeMillis() - timestart));
			return rsMap;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	public static void encasementMapping(String fieldClassName, String fieldName, ResultSet rs,
			Map<Object, Object> fieldValue) throws SQLException {
		// 优先规则：常用类型靠前
		if (fieldClassName.equals("java.lang.String")) {
			String s = rs.getString(fieldName);
			if (rs.wasNull()) {
				fieldValue.put(fieldName, null);
			} else {
				fieldValue.put(fieldName, s);
			}
		} else if (fieldClassName.equals("java.lang.Integer")) {
			int s = rs.getInt(fieldName);
			if (rs.wasNull()) {
				fieldValue.put(fieldName, null);
			} else {
				fieldValue.put(fieldName, s);// 早期jdk需要包装，jdk1.5后不需要包装
			}
		} else if (fieldClassName.equals("java.lang.Long")) {
			long s = rs.getLong(fieldName);
			if (rs.wasNull()) {
				fieldValue.put(fieldName, null);
			} else {
				fieldValue.put(fieldName, s);
			}
		} else if (fieldClassName.equals("java.lang.Boolean")) {
			boolean s = rs.getBoolean(fieldName);
			if (rs.wasNull()) {
				fieldValue.put(fieldName, null);
			} else {
				fieldValue.put(fieldName, s);
			}
		} else if (fieldClassName.equals("java.lang.Short")) {
			short s = rs.getShort(fieldName);
			if (rs.wasNull()) {
				fieldValue.put(fieldName, null);
			} else {
				fieldValue.put(fieldName, s);
			}
		} else if (fieldClassName.equals("java.lang.Float")) {
			float s = rs.getFloat(fieldName);
			if (rs.wasNull()) {
				fieldValue.put(fieldName, null);
			} else {
				fieldValue.put(fieldName, s);
			}
		} else if (fieldClassName.equals("java.lang.Double")) {
			double s = rs.getDouble(fieldName);
			if (rs.wasNull()) {
				fieldValue.put(fieldName, null);
			} else {
				fieldValue.put(fieldName, s);
			}
		} else if (fieldClassName.equals("java.sql.Timestamp")) {
			java.sql.Timestamp s = rs.getTimestamp(fieldName);
			if (rs.wasNull()) {
				fieldValue.put(fieldName, null);
			} else {
				fieldValue.put(fieldName, s);
			}
		} else if (fieldClassName.equals("java.sql.Date") || fieldClassName.equals("java.util.Date")) {
			java.util.Date s = rs.getDate(fieldName);
			if (rs.wasNull()) {
				fieldValue.put(fieldName, null);
			} else {
				fieldValue.put(fieldName, s);
			}
		} else if (fieldClassName.equals("java.sql.Time")) {
			java.sql.Time s = rs.getTime(fieldName);
			if (rs.wasNull()) {
				fieldValue.put(fieldName, null);
			} else {
				fieldValue.put(fieldName, s);
			}
		} else if (fieldClassName.equals("java.lang.Byte")) {
			byte s = rs.getByte(fieldName);
			if (rs.wasNull()) {
				fieldValue.put(fieldName, null);
			} else {
				fieldValue.put(fieldName, new Byte(s));
			}
		} else if (fieldClassName.equals("[B") || fieldClassName.equals("byte[]")) {
			// byte[]出现在SQL Server中
			byte[] s = rs.getBytes(fieldName);
			if (rs.wasNull()) {
				fieldValue.put(fieldName, null);
			} else {
				fieldValue.put(fieldName, s);
			}
		} else if (fieldClassName.equals("java.math.BigDecimal")) {
			BigDecimal s = rs.getBigDecimal(fieldName);
			if (rs.wasNull()) {
				fieldValue.put(fieldName, null);
			} else {
				fieldValue.put(fieldName, s);
			}
		} else if (fieldClassName.equals("java.lang.Object") || fieldClassName.equals("oracle.sql.STRUCT")) {
			Object s = rs.getObject(fieldName);
			if (rs.wasNull()) {
				fieldValue.put(fieldName, null);
			} else {
				fieldValue.put(fieldName, s);
			}
		} else if (fieldClassName.equals("java.sql.Array") || fieldClassName.equals("oracle.sql.ARRAY")) {
			java.sql.Array s = rs.getArray(fieldName);
			if (rs.wasNull()) {
				fieldValue.put(fieldName, null);
			} else {
				fieldValue.put(fieldName, s);
			}
		} else if (fieldClassName.equals("java.sql.Clob")) {
			java.sql.Clob s = rs.getClob(fieldName);
			if (rs.wasNull()) {
				fieldValue.put(fieldName, null);
			} else {
				fieldValue.put(fieldName, s);
			}
		} else if (fieldClassName.equals("java.sql.Blob")) {
			java.sql.Blob s = rs.getBlob(fieldName);
			if (rs.wasNull()) {
				fieldValue.put(fieldName, null);
			} else {
				fieldValue.put(fieldName, s);
			}
		} else {// 对于其它任何未知类型的处理
			Object s = rs.getObject(fieldName);
			if (rs.wasNull()) {
				fieldValue.put(fieldName, null);
			} else {
				fieldValue.put(fieldName, s);
			}
		}
	}
}
